"""
A database with a simplification or two.

easy Python table specification and validation
all tables have a single unique key
key zero is the default null value and should not be deleted
all foreign keys are the unique table key
primary keys are not enforced but can be checked by verification
foreign keys can get disconnected by delete

the future:
1. should i provide a deleted flag for each row
2. should i store history for each table
3. should i prevent delete
5. allow specification of width for each column
6. implement edit for large text objects
7. implement raw view
8. turn off filter?
"""
import gadfly
import sys
import os
import csv
import glob

class Col(object):
    """
    """
    def __init__(self):
        """
        """
        self.primary=False
        self.foreign=None

    def __repr__(self):
        """
        """
        return str(self.__dict__)

class Integer(Col):
    """
    """

    def __init__(self,fmt=''):
        """
        """
        Col.__init__(self)
        self.cnv=int
        self.fmt='%'+str(fmt)+'d'
        self.type='integer'
        self.default=0

class Float(Col):
    """
    """
    
    def __init__(self,fmt=''):
        """
        """
        Col.__init__(self)
        self.cnv=float
        self.fmt='%'+str(fmt)+'f'
        self.type='float'
        self.default=0.0

class String(Col):
    """
    """
    
    def __init__(self,fmt=8):
        """
        """
        Col.__init__(self)
        self.cnv=str
        self.fmt="'%"+str(fmt)+"s'"
        self.type='varchar'
        self.default=None

class Foreign(Integer):
    """
    """
    
    def __init__(self,table):
        """
        """
        Integer.__init__(self)
        self.foreign=table
    
def Primary(type_):
    """
    Return a primary key.
    """
    result=type_
    result.primary=True
    return result

class Sql_mix(object):
    """
    """
    
    def print_sql(self):
        """
        """
        for i in self.__dict__:
            if i.endswith('_sql'):
                print self.__dict__[i]
        
class DbRow(object):
    """
    """
    
    def __init__(self,parent,data):
        """
        """
        self.cols=parent.cols
        self.data=tuple(data)
        
    def keys(self):
        """
        """
        return tuple(self.cols)
    
    def values(self):
        """
        """
        return self.data
    
    def items(self):
        """
        """
        return tuple(zip(self.cols,self,data))
    
    def dict(self):
        """
        """
        return dict(self.items())

    def __getitem__(self,index):
        """
        """
        if not isinstance(index,int):
            index=self.cols.index(index.lower())
        return self.data[index]
    
    def __getattr__(self,attr):
        """
        """
        index=self.cols.index(attr.lower())
        return self.data[index]
    
    def __str__(self):
        """
        """
        return str(self.data)

class DbAccess(object):
    """
    """
    
    def __init__(self,cursor):
        """
        """
        self.cols=[x[0].lower() for x in cursor.description]
        self.data=[DbRow(self,x) for x in cursor.fetchall()]
        self.current=0
        
    def rows():
        """
        """
        return self.data
    
    def access(self,index):
        """
        """
        self.current=index
        
    def __getitem__(self,index):
        """
        """
        if not isinstance(index,int):
            colindex=self.cols.index(index.lower())
            return self.data[self.current][colindex]
        self.current=index
        return self.data[index]
    
    def __str__(self):
        """
        """
        return str(self.data[self.current])

class DbCol(Sql_mix):
    """
    """

    def __init__(self,db,table,name,attr):
        """
        """
        self.db=db
        self.sql=self.db.sql
        self.table=table
        self.name=name
        self.__dict__.update(attr.__dict__)
        if self.default is None:
            self.default=name+'_null'
        self.value=self.default
        self.update_sql='update %s set %s=? where key=?'%(table.name,name)
        self.select_sql='select %s from %s where key=?'%(name,table.name)
        if Db.debug:self.print_sql()

    def __setitem__(self,key,value):
        """
        """
        self.sql(self.update_sql,(value,key))

    def __getitem__(self,key):
        """
        """
        cursor=self.sql(self.select_sql,(key,))
        result= cursor.fetchone()
        return result[0]
        
    def __repr__(self):
        """
        """
        return '%s(%s, %s)' % (self.__class__, self.name, self.foreign)

class DbTable(Sql_mix):
    """
    """
    
    def __init__(self,db,name,kwds):
        """
        """
        self.cache = {}
        self.db=db
        self.sql=db.sql
        self.name=name
        self.cols=[None for i in range(len(kwds))]
        self.assoc=[]
        self.lookup={}
        self.head=[None for i in range(len(kwds))]
        self.primary_head=[]
        self.foreign_head=[]
        for idx, (k,v) in enumerate(kwds.items()):
            col=DbCol(db,self,k,v)
            self.cols[idx]=col
            self.lookup[col.name]=col
            self.head[idx]=col.name
            if col.foreign:
                self.foreign_head.append(col.name)
            if col.primary:
                self.primary_head.append(col.name)
            
        self.drop_table_sql='drop table %s'%self.name

        self.create_table_sql='create table %s (key integer'%self.name
        for c in self.cols:
            self.create_table_sql+=', %s %s'%(c.name,c.type)
        self.create_table_sql+=')'

        self.unique_index_sql='create unique index %skey on %s (key)'%(self.name,self.name)

        self.max_key_sql='select max(key) from %s'%self.name

        self.del_row_sql='delete from %s where key=?'%self.name

        select_key='select key'
        where_key=' where key =?'
        from_self=' from %s'%self.name
        
        self.get_rows_sql=select_key
        for c in self.head:self.get_rows_sql+=',%s'%c
        self.get_rows_sql+=from_self
        self.get_row_sql=self.get_rows_sql +where_key

        self.get_rows_primary_sql=select_key
        for c in self.primary_head:self.get_rows_primary_sql+=',%s'%c
        self.get_rows_primary_sql+=from_self
        self.get_row_primary_sql=self.get_rows_primary_sql+where_key
        
        self.get_rows_foreign_sql=select_key
        for c in self.foreign_head:self.get_rows_foreign_sql+=',%s'%c
        self.get_rows_foreign_sql+=from_self
        self.get_row_foreign_sql=self.get_rows_foreign_sql+where_key

        self.get_rows_key_sql=select_key+from_self
        self.get_row_key_sql=self.get_rows_key_sql+where_key

        self.new_row_sql='insert into %s (key'%self.name
        for c in self.cols:
            self.new_row_sql+=',%s'%c.name
        self.new_row_sql+=') values (?'
        for c in self.cols:
            self.new_row_sql+=','
            self.new_row_sql+=c.fmt%c.default
        self.new_row_sql+=')'

        if Db.debug:self.print_sql()
        Col.count=0

    def __repr__(self):
        """
        """
        return "%s(%s)" % (self.__class__.__name__, self.name)
    
    def drop(self):
        """
        """
        self.sql(self.drop_table_sql)

    def create_t(self):
        """
        """
        self.sql(self.create_table_sql)
        self.sql(self.unique_index_sql)
        self.sql(self.new_row_sql,(0,))

    def write_csv(self):
        """
        """
        writer=csv.writer(file('%s/%s.csv'%(self.db.dir,self.name),'w'))
        cursor=self.sql(self.get_rows_sql)
        dba=DbAccess(cursor)
        for row in dba.data:
            writer.writerow(row.values())

    def kfind(self, key):
        """
        (keyfind) helper for finding a row containing a particular key
        """
        for row in self:
            if row.key == key:
                return row      

    def gfind(self, **kwd):
        """
        (generalfind) helper for finding a row with a particular key, val pairs.
        multiple keywords are treated as 'and'.
        """
        for row in self:
            count = 0
            for key, val in kwd.items():
                if row[key] == val:
                    count +=1
            if count == len(kwd):
                return row
            
    def find(self, *arg, **kwd):
        """
        Invoke k or g find.
        find(3) is kfind(3)
        find(name='fred') is gfind(name='fred')
        cache results whenever it is a read only db operation.
        """
        if len(arg):
            assert len(arg) == 1 and len(kwd) == 0
            if not Db.read_only:
                return self.kfind(arg[0])
            a0 = arg[0]
            if a0 in self.cache:
                return self.cache[a0]
            self.cache[a0] = self.kfind(a0)
            return self.cache[a0]
        if not Db.read_only:
            return self.gfind(**kwd)
        skwd = str(kwd)
        if skwd in self.cache:
            return self.cache[skwd]
        self.cache[skwd] = self.gfind(**kwd)
        return self.cache[skwd]
        
    def __iter__(self):
        """
        Return all rows in order.
        """
        cursor=self.sql(self.get_rows_sql)
        dba=DbAccess(cursor)
        for row in dba.data:
            yield row
        
    def read_csv(self):
        """
        """
        
    def dump(self):
        """
        """
        print self
        print self.sql(self.get_rows_sql).pp()

    def report_error(self, message, row):
        """
        """
        self.errors.append('%s %s in %s %s' % (row[1:], message, self.name, self.get_row_nice(row[0], compact=True)))
        
    def verify(self):
        """
        1. check for foreign nul
        2. check for non-existing foreign
        3. check for non-primary
        4. check for null strings
        """
        self.errors = []
        prim = []
        for row in self.get_rows_primary()[1:]:
            if row[1:] in prim:
                self.report_error('duplicate primary %s' % self.primary_head, row)
            else:
                prim.append(row[1:])
        for row in self.get_rows()[1:]:
            for idx, value in enumerate(row[1:]):
                col = self.cols[idx]
                if col.foreign:
                    if value == 0:
                        self.report_error('foreign null %s' % col.name, row)
                    elif self.db.sql_count('select key from %s where key=%s' % (col.foreign, value))==0:
                        self.report_error('foreign %s has been deleted' %col.name, row)
                elif isinstance(value, str) and value.endswith('_null'):
                    self.report_error('null string', row)
        if self.errors:
            print '\n'.join(self.errors)
                    
    def repair(self):
        """
        1. patch non-existing foreign to null.
        """

    def keys(self):
        """
        """
        result=[]
        cursor=self.sql(self.get_rows_key_sql)
        for data in cursor.fetchall():
            if data[0]==0:continue
            result.append(data[0])
        return result
        
    def new(self):
        """
        """
        result=self.sql(self.max_key_sql)
        self.key=int(result.fetchone()[0])+1
        self.sql(self.new_row_sql,(self.key,))
        return self.key

    def append(self, **kwds):
        """
        """
        self.new()
        for key, val in kwds.items():
            assert key in self.lookup, key
            setattr(self, key, val)
            
    def oops(self):
        """
        """
        if self.key:
            self.sql(self.del_row_sql,(self.key,))

    def access(self,key):
        """
        """
        self.key=key

    def __getitem__(self,col):
        """
        """
        if Db.micro_debug:print self.__class__,'getitem',col
        assert col in self.lookup
        self.current=self.lookup[col]
        return self.current

    def  __getattr__(self, col):
       """
       """
       if Db.micro_debug:print self.__class__,'getattr',col
       if 'lookup' in self.__dict__ and col in self.__dict__['lookup']:
           return self.__getitem__(col)
       return object.__getattribute__(self, col)
       
    def __setitem__(self,col,value):
        """
        """
        if Db.micro_debug:print self.__class__,'setitem',col,value
        assert col in self.lookup
        self.lookup[col][self.key]=value

    def __setattr__(self, atr, val):
        """
        """
        if Db.micro_debug:print self.__class__,'setattr',atr,val
        if 'lookup' in self.__dict__ and atr in self.__dict__['lookup']:
            self.__setitem__(atr, val)
        object.__setattr__(self, atr, val)

    def get_rows(self):
        """
        """
        cursor=self.sql(self.get_rows_sql)
        return cursor.fetchall()    

    def get_rows_primary(self):
        """
        """
        cursor=self.sql(self.get_rows_primary_sql)
        return cursor.fetchall()    

    def get_rows_foreign(self):
        """
        """
        cursor=self.sql(self.get_rows_foreign_sql)
        return cursor.fetchall()    

    def get_rows_col(self,col):
        """
        """

    def get_rows_key(self):
        """
        """
        cursor=self.sql(self.get_rows_key_sql)
        return cursor.fetchall()    

    def get_rows_cols(self,*cols):
        """
        """
        
    def get_row(self,key):
        """
        """
        cursor=self.sql(self.get_row_sql,(key,))
        return cursor.fetchone()

    def get_row_nice(self, key, start=True, compact=False):
        """
        Use recursive descent to get some meaningful data (no keys)
        """
        result = []
        if start:
            result.append('Key:%s| ' % key)
        for c in self.cols:
            if c.foreign:
                try:
                    val = self.sql('select %s from %s where key=%s' % (c.name, self.name, key)).fetchone()[0]
                except:
                    val = '0'
                result.append(self.db[c.foreign].get_row_nice(int(val), start=False, compact=compact))
            else:
                try:
                    val = str(self.sql('select %s from %s where key=%s' % (c.name, self.name, key)).fetchone()[0])
                except:
                    val = 'missing'
                if compact:
                    val = val.strip()
                else:
                    val = val.rjust(10)
                result.append(val)
        return ' '.join(result)
        
    def get_row_primary(self,key):
        """
        """
        cursor=self.sql(self.get_row_primary_sql,(key,))
        return cursor.fetchone()

    def get_row_foreign(self,key):
        """
        """
        cursor=self.sql(self.get_row_foreign_sql,(key,))
        return cursor.fetchone()

    def get_row_col(self,key,col):
        """
        """

    def get_row_cols(self,key,*cols):
        """
        """

    def find_rows_all(self,where):
        """
        """
        
    def find_rows_primary(self,where):
        """
        """

    def find_rows_foreign(self,where):
        """
        """
        cursor=self.sql(self.get_row_foreign_sql,(key,))
        return cursor.fetchall()

    def find_rows_col(self,where,col):
        """
        """
        cursor=self.sql(self.get_row_col_sql,(where,col))
        return cursor.fetchall()

    def find_rows_key(self,where):
        """
        """

    def find_rows_cols(self,where,*cols):
        """
        """

    def validate(self):
        """
        """
        primary_count=0
        for col in self.cols:
            if col.primary:
                primary_count+=1
            if col.foreign:
                if not col.foreign in self.db.tables:
                    raise 'foreign table not found', col
                if not self.name in self.db.tables[col.foreign].assoc:
                    self.db.tables[col.foreign].assoc.append(self.name)
                if False and not col.foreign in self.assoc:
                    self.assoc.append(col.foreign)
        assert primary_count!=0, 'primary key not found %s' % self.name
        
class Db(object):
    """
    """
    debug = False
    micro_debug = False
    read_only = False

    def __init__(self, strict=True):
        """
        """
        self.strict = strict
        self.unique = [] # list of unique names
        self.name=name=self.__class__.__name__.lower()
        self.dir=name
        self.tables={}
        self.connection=None
        self.cursor=None
        self.define()
        self.validate_tables()

    def __repr__(self):
        """
        """
        return '%s(%s)' % (self.__class__.__name__, self.tables.keys())
    
    def define(self):
        """
        placeholder for user code
        """

    def populate(self):
        """
        palceholder for user code
        """
        
    def sql(self,cmd,arg=None):
        """
        """
        if self.connection is None:
            print 'auto connect'
            self.connect()
        if Db.debug:
            print 'sql[%s]' % cmd,
            if arg is not None:
                if len(arg) == 1:
                    print ('arg:%s' % arg[0]),
                else:
                    print ('args:%s' % str(arg)),
            print
        self.cursor.execute(cmd,arg)
        return self.cursor

    def sql_count(self, cmd, arg=None):
        """
        """
        result = self.sql(cmd, arg).fetchmany()
        return len(result)
        
    def create(self):
        """
        will only work once
        """
        try:
            os.mkdir(self.name)
            print self.name,'created'
        except OSError:
            print self.name,'exists'
            return
        self.connection=gadfly.gadfly()
        self.connection.startup(self.name,self.dir)
        self.cursor=self.connection.cursor()
        self.all_tables('create_t')

    def kill(self):
        """
        use with care you data will be deleted...
        """
        if os.path.exists(self.name):
            for f in glob.glob('%s/*' % self.name):
                os.remove(f)
            os.rmdir(self.name)
            
    def connect(self):
        """
        """
        self.connection=gadfly.gadfly(self.name,self.dir)
        self.cursor=self.connection.cursor()

    def commit(self):
        """
        """
        if self.connection:
            self.connection.commit()
        
    def add_table(self,_name,**kwds):
        """
        """
        if self.strict:
            assert not _name in self.unique, _name
            self.unique.append(_name)
            for k in kwds:
                assert not k in self.unique, k
                self.unique.append(k)
        assert not hasattr(self, _name), _name
        self.tables[_name]=DbTable(self,_name,kwds)
        setattr(self, _name, self.tables[_name]) 

    def validate_tables(self):
        """
        Check table integrity.
        """
        self.all_tables('validate')

    def verify_tables(self):
        """
        Check data integrity.
        """
        self.all_tables('verify')

    def all_tables(self,what):
        """
        """
        for table in self.tables.values():
            getattr(table,what)()

    def __iter__(self):
        """
        Return a list of all the tables.
        """
        for table in self.tables.values():
            yield table
            
    def drop_tables(self):
        """
        """
        self.all_tables('drop')
        
    def introspect(self):
        """
        """
        print self.sql('select * from __table_names__').pp()
        print self.sql('select * from __columns__').pp()
        print self.sql('select * from __indices__').pp()
        print self.sql('select * from __indexcols__').pp()
        print self.sql('select * from __datadefs__').pp()
        
    def new_data(self, what, **kwds):
        """
        """
        DATA=self[what]
        DATA.new()
        for key, value in kwds.items():
            DATA[key]  = value

    def mod_data(self, what, key, **kwds):
        """
        """
        assert len(kwds)==1
        atr, val = kwds.popitem()
        table = self[what]
        table.access(key)
        table[atr] = val
        
    def __getitem__(self,key):
        """
        """
        self.current=self.tables[key]
        return self.current

